package com.lwlk.zdk.service;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.lwlk.zdk.service.User.SysUserService;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;


@Service
public class ExcelService {
	@Autowired
	static
	SysUserService userService;

	public List<List<String>> readXls(String file) throws Exception { return readXls(file, 1, true); }
	  /**
	   * 获取excel文件内容
	   */
	public static List<List<String>> readXls(String file, int startRow, boolean readAllSheet) throws Exception {
		if (file == null) {
			throw new Exception("获取excel文件路径失败");
		}
	
		String fileType = file.substring(file.lastIndexOf(".") + 1, file.length());
		InputStream is = null;
		try {
			File files=new File(file);
			is = new FileInputStream(files);
		} catch (Exception e) {
			throw new Exception("获取和解析excel文件失败");
		} 
		
		XSSFWorkbook xSSFWorkbook = null;
		HSSFWorkbook hSSFWorkbook = null;
		int total = 0;
		
		if (fileType.equals("xls")) {
			hSSFWorkbook = new HSSFWorkbook(is);
			total = hSSFWorkbook.getNumberOfSheets();
		} else if (fileType.equals("xlsx")) {
			xSSFWorkbook = new XSSFWorkbook(is);
			total = xSSFWorkbook.getNumberOfSheets();
		} else {
			throw new Exception("非excel文件");
		} 
		int rowCount = 0;
		List<List<String>> result = new ArrayList<List<String>>();
		
		if (!readAllSheet) {
			total = 1;
		}
		try {
	    	if (fileType.equals("xls")) {
	    		for (int numSheet = 0; numSheet < total; numSheet++) {
	  		      Sheet hssfSheet = hSSFWorkbook.getSheetAt(numSheet);
	  		      if (hssfSheet != null) {
	  	
	  		        
	  		        rowCount += hssfSheet.getLastRowNum();
	  		        
	  		        for (int rowNum = startRow; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
	  		          Row hssfRow = hssfSheet.getRow(rowNum);
	  		          if (hssfRow != null) {
	  	
	  		            
	  		            int minColIx = hssfRow.getFirstCellNum();
	  		            int maxColIx = hssfRow.getLastCellNum();
	  		            int empty = 0;
	  		            List<String> rowList = new ArrayList<String>();
	  		            if (minColIx >= 0 && maxColIx >= 0) {
	  		            	boolean b = false;
	  		            	for (int colIx = minColIx; colIx < maxColIx; colIx++) {
	  		                Cell cell = hssfRow.getCell(colIx);
	  		                System.out.println(cell);
	  		                if (cell == null) {
	  		                  cell = hssfRow.createCell(colIx);
	  		                  cell.setCellValue("");
	  		                  empty++;
	  		                } else if (cell.toString().matches("\\s*")) {
	  		                  empty++;
	  		                }
	  		                String content = getExcelValue1(cell).replace(" ", "");
	  		                rowList.add(content);
	  		                if(!StringUtils.isEmpty(content)) {
	  		                	b = true;
	  		                }
	  		              } 
	  		              
	  		              System.out.println(empty);
	  		              if (empty == maxColIx) {
	  		                break;
	  		              }
	  		            	if(!b || rowList.size() == 0) {
	  		            		continue;
	  		            	}
	  		              result.add(rowList); } 
	  		          } 
	  		        } 
	  		      } 
	  		    }
	  	    } else if (fileType.equals("xlsx")) {
	  	    	for (int numSheet = 0; numSheet < total; numSheet++) {
	  		      Sheet hssfSheet = xSSFWorkbook.getSheetAt(numSheet);
	  		      if (hssfSheet != null) {
	  		        
	  		        rowCount += hssfSheet.getLastRowNum();
	  		        
	  		        for (int rowNum = startRow; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
	  		          Row hssfRow = hssfSheet.getRow(rowNum);
	  		          if (hssfRow != null) {
	  	
	  		            
	  		            int minColIx = hssfRow.getFirstCellNum();
	  		            int maxColIx = hssfRow.getLastCellNum();
	  		            int empty = 0;
	  		            List<String> rowList = new ArrayList<String>();
	  		            if (minColIx >= 0 && maxColIx >= 0) {
	  		            	boolean b = false;
	  		            	for (int colIx = minColIx; colIx < maxColIx; colIx++) {
		  		                Cell cell = hssfRow.getCell(colIx);
		  		                System.out.println(cell);
		  		                if (cell == null) {
		  		                	cell = hssfRow.createCell(colIx);
		  		                	cell.setCellValue("");
		  		                	empty++;
		  		                } else if (cell.toString().matches("\\s*")) {
		  		                	empty++;
		  		                }
		  		                String content = getExcelValue1(cell).replace(" ", "");
		  		                rowList.add(content);
		  		                if(!StringUtils.isEmpty(content)) {
		  		                	b = true;
		  		                }
	  		            	} 
	  		              
	  		            	System.out.println(empty);
	  		            	if (empty == maxColIx) {
	  		            		break;
	  		            	}
	  		            	if(!b || rowList.size() == 0) {
	  		            		continue;
	  		            	}
	  		              
	  		              result.add(rowList); } 
	  		          } 
	  		        } 
	  		      } 
	  		    }
	  	    }
	    } catch(Exception e) {
	    	throw new Exception("excel文件数据结构错误");
	    }
	   /* if (fileType.equals("xls")) {
		      hSSFWorkbook.close();
		} else if (fileType.equals("xlsx")) {
			xSSFWorkbook.close();
		}
	    */
	    if(result.size() == 0) {
	    	throw new Exception("excel文件无有效数据");
	    }
		return result;
	}


	  /**
		 * 获得单元格值 针对03版
		 * @param cell
		 * @return
		 */
	public static String getExcelValue(Cell cell){
		String cellValue=null;
		if(cell != null){
			//设置字体编码
			//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			// 以下是判断数据的类型
			switch (cell.getCellType()) {
				case HSSFCell.CELL_TYPE_NUMERIC: // 数字
					//DecimalFormat df = new DecimalFormat("##。###");
					//cellValue = df.format(cell.getNumericCellValue());
					DecimalFormat df = new DecimalFormat("##.###");
					cellValue = df.format(cell.getNumericCellValue());	
					
							 // 判断是否是日期类型
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								Date dateCellValue = cell.getDateCellValue();
								cellValue = DateFormatUtils.format(dateCellValue, "yyyy-MM-dd");
							} else {
								cellValue = df.format(cell.getNumericCellValue());// 取整
								Double doubleVal = cell.getNumericCellValue();// 取double
								if (Double.parseDouble(cellValue + ".0") != doubleVal) { // 判断是否含有小数位.0
									cellValue = df.format(doubleVal).replaceAll("0*$", "");
								}
							}
							break;
						case HSSFCell.CELL_TYPE_STRING: // 字符串
							cellValue = cell.getStringCellValue();
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
							cellValue = cell.getBooleanCellValue() + "";
							break;
						case HSSFCell.CELL_TYPE_FORMULA: // 公式
							cellValue = cell.getCellFormula() + "";
							break;
						case HSSFCell.CELL_TYPE_BLANK: // 空值
							cellValue = "";
							break;
						case HSSFCell.CELL_TYPE_ERROR: // 故障
							cellValue = "非法字符";
							break;
						default:
							cellValue = "未知类型";
							break;
						}
		
				 }
				 return cellValue;
			 //return replaceBlank(cellValue);
	}
		/**
		 * 获得单元格的值 07版后
		 * @param cell
		 * @return
		 */
	public static String getExcelValue1(Cell cell){
		String cellValue=null;
		if(cell != null){
			switch (cell.getCellType()) {
				case HSSFCell.CELL_TYPE_NUMERIC: // 数字
					DecimalFormat df = new DecimalFormat("##.###");
					cellValue = df.format(cell.getNumericCellValue());	
					
					// 判断是否是日期类型
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						Date dateCellValue = cell.getDateCellValue();
						cellValue = DateFormatUtils.format(dateCellValue, "yyyy-MM-dd");
					} else {
						cellValue = df.format(cell.getNumericCellValue());// 取整
						Double doubleVal = cell.getNumericCellValue();// 取double
						if (Double.parseDouble(cellValue + ".0") != doubleVal) { // 判断是否含有小数位.0
							cellValue = df.format(doubleVal).replaceAll("0*$", "");
						}
					}
					break;
				case HSSFCell.CELL_TYPE_STRING: // 字符串
					cellValue = cell.getStringCellValue();
					break;
				case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
					cellValue = cell.getBooleanCellValue() + "";
					break;
				case HSSFCell.CELL_TYPE_FORMULA: // 公式
					cellValue = cell.getCellFormula() + "";
					break;
				case HSSFCell.CELL_TYPE_BLANK: // 空值
					cellValue = "";
					break;
				case HSSFCell.CELL_TYPE_ERROR: // 故障
					cellValue = "非法字符";
					break;
				default:
					cellValue = "未知类型";
					break;
			}
		}
		return cellValue;
	}

	/**
	 * @Description 判断单元格内容格式 
	 * @author yzp 
	 * @param cell
	 * @return
	 */
/*	public static String getStringVal(Cell cell) {
		switch (cell.getCellTypeEnum().ordinal()) {
			case 1:
				return ((int)cell.getNumericCellValue())+"";
			case 2:
				cell.setCellType(1);
				return cell.getStringCellValue();
			case 3:
				return cell.getRichStringCellValue().toString();
			case 4:
				return cell.getCellFormula();
			case 5:
				return "";
			case 6:
				return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
			case 7:
				return ErrorEval.getText(cell.getErrorCellValue());
		} 
		return " ";
	}*/
}
